package com.sanalkitaplik.hibernateutil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

public class AuthorizationsUtil {
	
	private static Session session = null;
	private static Connection connection = null;
	private static ArrayList<String> authorizationList = null;
	private static ArrayList<String> groupNamesList = null;
	private static long groupId;
	private static long authId;
	
	@SuppressWarnings("rawtypes")
								/* Sistemde kayıtlı group_name'leri döndürür. */
	public static ArrayList<String> getGroupNames(){
		try{
			session = SessionFactoryBuilder.getSessionFactory().openSession(); 
			
			Query query = session.createSQLQuery("select GROUP_NAME from SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS");
			
			List list = query.list();
			
			groupNamesList = new ArrayList<String> ();
			
			for (Iterator iterator = list.iterator(); iterator.hasNext();) {
				groupNamesList.add((String) iterator.next());
			}
			
		}
		catch(Exception ex){
			System.err.println("An exception occured while getting group names : " +ex.getMessage());
		}
		finally{
			session.flush();
			session.close();
		}
		return groupNamesList;
	}
	
	@SuppressWarnings("rawtypes")
								/* Sistemde kayıtlı authorization_name'leri döndürür. */
	public static ArrayList<String> getAuthorizations(){
		try{
			session = SessionFactoryBuilder.getSessionFactory().openSession(); 
			
			Query query = session.createSQLQuery("select AUTHORIZATION_NAME from SNLKTPLK_ADMIN_AUTHORIZATIONS");
			
			List list = query.list();
			
			authorizationList = new ArrayList<String> ();
			
			for (Iterator iterator = list.iterator(); iterator.hasNext();) {
				authorizationList.add((String) iterator.next());
			}
			
		}
		catch(Exception ex){
			System.err.println("An exception occured while getting authorization names : " +ex.getMessage());
		}
		finally{
			session.flush();
			session.close();
		}
		return authorizationList;
	}
	
	@SuppressWarnings("rawtypes")
	public static ArrayList<String> getGroupAuthorizations(String group_name){
		try{
			session = SessionFactoryBuilder.getSessionFactory().openSession(); 
			
			Query query = session.createSQLQuery(
					  "select AUTHORIZATION_NAME from SNLKTPLK_ADMIN_AUTHORIZATIONS "
					+ "where AUTHORIZATION_ID in (select aga.AUTHORIZATION_ID from "
					+ "SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS as aag join SNLKTPLK_ADMIN_GROUPS_AUTHORIZATIONS as aga "
					+ "on aag.GROUP_ID = aga.GROUP_ID where aag.GROUP_NAME = '"+group_name+"')");
			
			List list = query.list();
			
			authorizationList = new ArrayList<String> ();
			
			for (Iterator iterator = list.iterator(); iterator.hasNext();) 
				authorizationList.add((String) iterator.next());
			
		}
		catch(Exception ex){
			System.out.println("An exception occured while getting group authorizations : " +ex.getMessage());
		}
		finally{
			session.flush();
			session.close();
		}
		return authorizationList;
	}
	
	/* Admin'i sisteme ekleyemezse geriye false döndürecek.*/
	public static boolean addAdminGroup(String group_name) throws SQLException{
		Statement stmt = null;
		boolean flag = true;
		try{
			if(groupNameIsUnique(group_name)){
				connection = Connect.getConnection();
		    	stmt = connection.createStatement();

				String sql = "insert into SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS values (default,'"+group_name+"')";
		    	stmt.executeUpdate(sql);
			}
			else
				flag = false;
		}
		catch(Exception ex){
			flag = false;
			System.out.println("An exception occured while adding an admin to a group : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
		}
		
		if(flag) return true;
		else return false;
	}
	
	/* Silinecek grubu bulamazsa false döner.*/
	public static boolean removeAdminGroup(String group_name) throws SQLException{
		Statement stmt = null;
		boolean flag = true;
		try{
			if(!groupNameIsUnique(group_name)){
				connection = Connect.getConnection();
		    	stmt = connection.createStatement();

				String sql = "delete from SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS where GROUP_ID = " + groupId;
		    	stmt.executeUpdate(sql);
			}
			else
				flag = false;
		}
		catch(Exception ex){
			flag = false;
			System.out.println("An exception occured while removing the group : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
		}
		
		if(flag) return true;
		else return false;
	}
	
	/* Authorization'ı gruba ekleyemezse geriye false döndürecek.*/
	public static boolean addAuthorizationToGroup(String group_name, String auth_name) throws SQLException{
		Statement stmt = null;
		boolean flag = true;
		try{
			if(groupNameIsUnique(group_name))//group_name'in unique olması tabloda yok demek,group_id belirlendi.
				return false; 
        	
			if(!authNameIsUnique(auth_name)){ //ekleyeceğimiz auth.lar zaten tabloda olanlardan olacak, unique olmamalı!
				connection = Connect.getConnection();
				stmt = connection.createStatement();

    			String sql = "insert into SNLKTPLK_ADMIN_GROUPS_AUTHORIZATIONS values ("+groupId+","+authId+")";
    	    	stmt.executeUpdate(sql);
        	}
        	else
        		return false;
		}
		catch(Exception ex){
			flag = false;
			System.err.println("An exception occured while adding an authorization to a group : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
		}
		
		return flag;
	}
	
	/* Gruba ait olan yetkiler silecek. */
	public static boolean deleteAuthorizationsByGroup(String group_name) throws SQLException{
		Statement stmt = null;
		boolean flag = true;
		try{
			if(groupNameIsUnique(group_name))//group_name'in unique olması tabloda yok demek,group_id belirlendi.
				return false; 
        	
			connection = Connect.getConnection();
			stmt = connection.createStatement();

			String sql = "delete from SNLKTPLK_ADMIN_GROUPS_AUTHORIZATIONS where GROUP_ID = " + groupId;
			stmt.executeUpdate(sql);
		}
		catch(Exception ex){
			flag = false;
			System.err.println("An exception occured while deleting authorizations from the group : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
		}
		
		return flag;
	}
	
	/* Authorization_group_name unique ise true, zaten tabloda kayıtlıysa false döndürecek.*/
	private static boolean groupNameIsUnique(String groupName) throws SQLException{
		Statement stmt = null;
		ResultSet rs = null;
		boolean flag = true;
		try{
			connection = Connect.getConnection();
			stmt = connection.createStatement();
			String sql = "select GROUP_ID from SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS " +
								"where GROUP_NAME = '"+groupName+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				flag = false;
				groupId = rs.getLong("GROUP_ID");
			}
		}
		catch(Exception ex){
			System.out.println("An exception occured while searching group_name : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
			rs.close();
		}
		return flag;
	}
	
	private static boolean authNameIsUnique(String authName) throws SQLException{
		Statement stmt = null;
		ResultSet rs = null;
		boolean flag = true;
		try{
			connection = Connect.getConnection();
			stmt = connection.createStatement();
			String sql = "select AUTHORIZATION_ID from SNLKTPLK_ADMIN_AUTHORIZATIONS " +
							"where AUTHORIZATION_NAME = '"+authName+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				flag = false;
				authId = rs.getLong("AUTHORIZATION_ID");
			}
		}
		catch(Exception ex){
			System.out.println("An exception occured while searching group_name : " +ex.getMessage());
		}
		finally{
			connection.close();
			stmt.close();
			rs.close();
		}
		
		return flag;
	}
	
	public static void main(String[] args) throws SQLException{
		System.out.println(addAuthorizationToGroup("myNewGroup", "addAdmin"));
//		for(java.util.Iterator<String> it = getGroupAuthorizations("topGroup").iterator(); it.hasNext();){
//			System.out.println(it.next());
//		}
	}
}
